﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Collections;
using System.Reflection;

namespace ERPU8WebApi.Until
{
    public class SqlHelper
    {
        public static string ConnErp = System.Configuration.ConfigurationSettings.AppSettings["ConnErp"].ToString();
        public static string ConnErpTool = "";
        public static string ConnBpm = "";
        public static int InventoryPageCount = int.Parse(System.Configuration.ConfigurationSettings.AppSettings["InventoryPageCount"].ToString());

        /// <summary>
        /// 返回DataTable,不带参数
        /// </summary>
        /// <param name="comm"></param>
        /// <returns></returns>
        public static DataTable DataTable(SqlCommand comm)
        {
            try
            {
                SqlCommand sqlCommand = comm;
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);

                DataTable datatable = new DataTable();
                sqlDataAdapter.Fill(datatable);
                sqlCommand.Dispose();
                return datatable;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        /// <summary>
        /// 返回DataSet，不带参数
        /// </summary>
        /// <param name="comm"></param>
        /// <returns></returns>
        public static DataSet DataSet(SqlCommand comm)
        {
            try
            {
                SqlCommand sqlCommand = comm;
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                DataSet dataset = new DataSet();
                sqlDataAdapter.Fill(dataset);
                sqlCommand.Dispose();
                return dataset;
            }
            catch (SqlException ex)
            {
                throw ex;

            }
            catch (Exception ex)
            {
                throw ex;

            }

        }
      
        /// <summary>
        /// 操作数据库方法
        /// </summary>
        /// <param name="str"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string str, SqlParameter[] sql, int type)
        {
            SqlConnection sqlConnection = new SqlConnection();
            try
            {
                if (type == 0)
                {
                    sqlConnection.ConnectionString = ConnErp;
                }
                else if (type == 1)
                {
                    sqlConnection.ConnectionString = ConnErpTool;
                }
                else if (type == 2)
                {
                    sqlConnection.ConnectionString = ConnBpm;
                }
                sqlConnection.Open();
                SqlCommand SqlCommand = new SqlCommand(str, sqlConnection);
                SqlCommand.Parameters.AddRange(sql);
                int ret = 0;
                ret = SqlCommand.ExecuteNonQuery();
                sqlConnection.Close();
                sqlConnection.Dispose();
                SqlCommand.Dispose();
                return ret;


            }
            catch (Exception E)
            {
                if (sqlConnection.State == ConnectionState.Open)
                {
                    sqlConnection.Close();
                    sqlConnection.Dispose();
                }
                throw E;
            }
        }
        public static int NewExecuteNonQuery(string str, SqlParameter[] sql, int type)
        {
            SqlConnection sqlConnection = new SqlConnection();
            try
            {
                if (type == 0)
                {
                    sqlConnection.ConnectionString = ConnErp;
                }
                else if (type == 1)
                {
                    sqlConnection.ConnectionString = ConnErpTool;
                }
                else if (type == 2)
                {
                    sqlConnection.ConnectionString = ConnBpm;
                }
                sqlConnection.Open();
                SqlCommand SqlCommand = new SqlCommand(str, sqlConnection);
                SqlCommand.Parameters.AddRange(sql);
                int ret = 0;
                ret = SqlCommand.ExecuteNonQuery();
                sqlConnection.Close();
                sqlConnection.Dispose();
                SqlCommand.Dispose();
                return ret;


            }
            catch (Exception E)
            {
                if (sqlConnection.State == ConnectionState.Open)
                {
                    sqlConnection.Close();
                    sqlConnection.Dispose();
                }
                throw E;
            }
        }
        /// <summary>
        /// 操作数据库，执行sql语句，不带参数和事务处理
        /// </summary>
        /// <param name="str"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public  int ExecuteNonQuery(string str, int type)
        {
            SqlConnection sqlConnection = new SqlConnection();
            try
            {
                if (type == 0)
                {
                    sqlConnection.ConnectionString = ConnErp;
                }
                else if (type == 1)
                {
                    sqlConnection.ConnectionString = ConnErpTool;
                }
                else if (type == 2)
                {
                    sqlConnection.ConnectionString = ConnBpm;
                }
                sqlConnection.Open();
                SqlCommand SqlCommand = new SqlCommand(str, sqlConnection);
                int ret = 0;
                ret = SqlCommand.ExecuteNonQuery();
                sqlConnection.Close();
                sqlConnection.Dispose();
                SqlCommand.Dispose();
                return ret;
            }
            catch (Exception E)
            {
                if (sqlConnection.State == ConnectionState.Open)
                {
                    sqlConnection.Close();
                    sqlConnection.Dispose();
                }
                throw E;
            }
        }

        /// <summary>
        /// 查询方法 带数组参数数组
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string strSql, SqlParameter[] sqlParameter, int type)
        {
            SqlConnection sqlConnection = new SqlConnection();
            try
            {
                if (type == 0)
                {
                    sqlConnection.ConnectionString = ConnErp;
                }
                else if (type == 1)
                {
                    sqlConnection.ConnectionString = ConnErpTool;
                }
                else if (type == 2)
                {
                    sqlConnection.ConnectionString = ConnBpm;
                }
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand(strSql, sqlConnection);
                sqlCommand.Parameters.AddRange(sqlParameter);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = sqlCommand;
                DataSet dataSet = new DataSet();
                sqlDataAdapter.Fill(dataSet, "s");
                sqlConnection.Close();
                sqlConnection.Dispose();
                sqlCommand.Dispose();
                return dataSet;
            }
            catch (SqlException ex)
            {
                if (sqlConnection.State == ConnectionState.Open)
                {
                    sqlConnection.Close();
                    sqlConnection.Dispose();
                }
                throw ex;
            }
            catch (Exception ex)
            {
                if (sqlConnection.State == ConnectionState.Open)
                {
                    sqlConnection.Close();
                    sqlConnection.Dispose();
                }
                throw ex;
            }

        }

        /// <summary>
        /// 查询方法 无数组参数数组
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public  DataSet ExecuteDataSetSql(string strSql, int type)
        {
            SqlConnection sqlConnection = new SqlConnection();
            try
            {
                if (type == 0)
                {
                    sqlConnection.ConnectionString = ConnErp;
                }
                else if (type == 1)
                {
                    sqlConnection.ConnectionString = ConnErpTool;
                }
                else if (type == 2)
                {
                    sqlConnection.ConnectionString = ConnBpm;
                }
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand(strSql, sqlConnection);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = sqlCommand;
                DataSet dataSet = new DataSet();
                sqlDataAdapter.Fill(dataSet, "s");

                sqlConnection.Close();
                sqlConnection.Dispose();
                sqlCommand.Dispose();
                return dataSet;
            }
            catch (SqlException ex)
            {
                if (sqlConnection.State == ConnectionState.Open)
                {
                    sqlConnection.Close();
                    sqlConnection.Dispose();
                }
                throw ex;
            }
            catch (Exception ex)
            {
                if (sqlConnection.State == ConnectionState.Open)
                {
                    sqlConnection.Close();
                    sqlConnection.Dispose();
                }
                throw ex;
            }

        }
        /// <summary>
        /// 返回数据集DataTable
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static DataTable DataTable(string strSql, int type)
        {
            SqlConnection sqlConnection = new SqlConnection();

            try
            {
                if (type == 0)
                {
                    sqlConnection.ConnectionString = ConnErp;
                }
                else if (type == 1)
                {
                    sqlConnection.ConnectionString = ConnErpTool;
                }
                else if (type == 2)
                {
                    sqlConnection.ConnectionString = ConnBpm;
                }
                sqlConnection.Open();

                SqlCommand sqlCommand = new SqlCommand(strSql, sqlConnection);

                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = sqlCommand;
                DataTable datatable = new DataTable();
                sqlDataAdapter.Fill(datatable);

                sqlConnection.Close();
                sqlConnection.Dispose();
                sqlCommand.Dispose();
                return datatable;
            }
            catch (SqlException ex)
            {
                if (sqlConnection.State == ConnectionState.Open)
                {
                    sqlConnection.Close();
                    sqlConnection.Dispose();
                }
                throw ex;
            }
            catch (Exception ex)
            {
                if (sqlConnection.State == ConnectionState.Open)
                {
                    sqlConnection.Close();
                    sqlConnection.Dispose();
                }
                throw ex;
            }
        }
        public static DataTable DataTable(SqlTransaction trans, string str)
        {
            try
            {
                if (trans.Connection.State == ConnectionState.Closed)
                {
                    trans.Connection.Open();
                }
                SqlCommand command = new SqlCommand(str, trans.Connection);
                command.Transaction = trans;
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = command;
                DataTable datatable = new DataTable();
                sqlDataAdapter.Fill(datatable);

                return datatable;
            }
            catch (Exception E)
            {
                trans.Connection.Close();
                throw E;
            }
        }
        /// <summary>
        /// 返回数据库DataTable带参数数组,登录，修改密码用最新的数据库进行判断和修改
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="sqlParameter"></param>
        /// <returns></returns>
        public static DataTable NewDataTable(string strSql, SqlParameter[] sqlParameter, int type)
        {
            SqlConnection sqlConnection = new SqlConnection();
            try
            {

                if (type == 0)
                {
                    sqlConnection.ConnectionString = ConnErp;
                }
                else if (type == 1)
                {
                    sqlConnection.ConnectionString = ConnErpTool;
                }
                else if (type == 2)
                {
                    sqlConnection.ConnectionString = ConnBpm;
                }

                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand(strSql, sqlConnection);
                sqlCommand.Parameters.AddRange(sqlParameter);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = sqlCommand;
                DataTable datatable = new DataTable();
                sqlDataAdapter.Fill(datatable);

                sqlConnection.Close();
                sqlConnection.Dispose();
                sqlCommand.Dispose();
                return datatable;
            }
            catch (SqlException ex)
            {
                if (sqlConnection.State == ConnectionState.Open)
                {
                    sqlConnection.Close();
                    sqlConnection.Dispose();
                }
                throw ex;
            }
            catch (Exception ex)
            {
                if (sqlConnection.State == ConnectionState.Open)
                {
                    sqlConnection.Close();
                    sqlConnection.Dispose();
                }
                throw ex;
            }
        }
        /// <summary>
        /// 返回数据库DataTable带参数数组
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="sqlParameter"></param>
        /// <returns></returns>
        public static DataTable DataTable(string strSql, SqlParameter[] sqlParameter, int type)
        {
            SqlConnection sqlConnection = new SqlConnection();
            try
            {

                if (type == 0)
                {
                    sqlConnection.ConnectionString = ConnErp;
                }
                else if (type == 1)
                {
                    sqlConnection.ConnectionString = ConnErpTool;
                }
                else if (type == 2)
                {
                    sqlConnection.ConnectionString = ConnBpm;
                }
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand(strSql, sqlConnection);
                sqlCommand.Parameters.AddRange(sqlParameter);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = sqlCommand;
                DataTable datatable = new DataTable();
                sqlDataAdapter.Fill(datatable);

                sqlConnection.Close();
                sqlConnection.Dispose();
                sqlCommand.Dispose();
                return datatable;
            }
            catch (SqlException ex)
            {
                if (sqlConnection.State == ConnectionState.Open)
                {
                    sqlConnection.Close();
                    sqlConnection.Dispose();
                }
                throw ex;
            }
            catch (Exception ex)
            {
                if (sqlConnection.State == ConnectionState.Open)
                {
                    sqlConnection.Close();
                    sqlConnection.Dispose();
                }
                throw ex;
            }
        }

        /// <summary>
        /// 返回数据集DataSet
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static DataSet DataSet(string strSql, int type)
        {
            SqlConnection sqlConnection = new SqlConnection();
            try
            {
                if (type == 0)
                {
                    sqlConnection.ConnectionString = ConnErp;
                }
                else if (type == 1)
                {
                    sqlConnection.ConnectionString = ConnErpTool;
                }
                else if (type == 2)
                {
                    sqlConnection.ConnectionString = ConnBpm;
                }
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand(strSql, sqlConnection);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = sqlCommand;
                DataSet dataset = new DataSet();
                sqlDataAdapter.Fill(dataset);

                sqlConnection.Close();
                sqlConnection.Dispose();
                sqlCommand.Dispose();
                return dataset;
            }
            catch (SqlException ex)
            {
                if (sqlConnection.State == ConnectionState.Open)
                {
                    sqlConnection.Close();
                    sqlConnection.Dispose();
                }
                throw ex;
            }
            catch (Exception ex)
            {
                if (sqlConnection.State == ConnectionState.Open)
                {
                    sqlConnection.Close();
                    sqlConnection.Dispose();
                }
                throw ex;
            }

        }
        /// <summary>
        /// 读数据 无数组参数数组
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string cmdText, int type)
        {
            SqlConnection sqlConnection = new SqlConnection();

            if (type == 0)
            {
                sqlConnection.ConnectionString = ConnErp;
            }
            else if (type == 1)
            {
                sqlConnection.ConnectionString = ConnErpTool;
            }
            else if (type == 2)
            {
                sqlConnection.ConnectionString = ConnBpm;
            }
            SqlCommand cmd = new SqlCommand(cmdText, sqlConnection);
            SqlDataReader sdr = null;

            try
            {
                sqlConnection.Open();
                sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                return sdr;
            }
            catch
            {
                if (sqlConnection.State == ConnectionState.Open)
                {
                    sqlConnection.Close();
                    sqlConnection.Dispose();
                }
                cmd.Dispose();
                throw;
            }
        }
        /// <summary>
        /// 读数据 带数组参数数组
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string cmdText, SqlParameter[] cmdParms, int type)
        {
            SqlConnection sqlConnection = new SqlConnection();

            if (type == 0)
            {
                sqlConnection.ConnectionString = ConnErp;
            }
            else if (type == 1)
            {
                sqlConnection.ConnectionString = ConnErpTool;
            }
            else if (type == 2)
            {
                sqlConnection.ConnectionString = ConnBpm;
            }
            SqlCommand cmd = new SqlCommand(cmdText, sqlConnection);


            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
            SqlDataReader sdr = null;
            try
            {
                sqlConnection.Open();
                sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();

                return sdr;
            }
            catch
            {

                cmd.Dispose();
                if (sqlConnection.State == ConnectionState.Open)
                {
                    sqlConnection.Close();
                    sqlConnection.Dispose();
                }
                throw;
            }
        }

        /// <summary>
        /// 返回第一行第一列数据
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string cmdText, int type)
        {
            SqlConnection sqlConnection = new SqlConnection();

            if (type == 0)
            {
                sqlConnection.ConnectionString = ConnErp;
            }
            else if (type == 1)
            {
                sqlConnection.ConnectionString = ConnErpTool;
            }
            else if (type == 2)
            {
                sqlConnection.ConnectionString = ConnBpm;
            }

            SqlCommand cmd = new SqlCommand(cmdText, sqlConnection);

            sqlConnection.Open();
            object val = cmd.ExecuteScalar();
            sqlConnection.Close();
            sqlConnection.Dispose();
            cmd.Dispose();

            return val;
        }
        /// <summary>
        /// 返回受影响的行数
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string cmdText, SqlParameter[] cmdParms, int type)
        {
            SqlConnection sqlConnection = new SqlConnection();

            if (type == 0)
            {
                sqlConnection.ConnectionString = ConnErp;
            }
            else if (type == 1)
            {
                sqlConnection.ConnectionString = ConnErpTool;
            }
            else if (type == 2)
            {
                sqlConnection.ConnectionString = ConnBpm;
            }

            SqlCommand cmd = new SqlCommand(cmdText, sqlConnection);
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
            sqlConnection.Open();
            object val = cmd.ExecuteScalar();
            sqlConnection.Close();
            sqlConnection.Dispose();
            cmd.Dispose();

            return val;
        }
        /// <summary>
        /// 返回受影响的行数
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public static object ExecuteScalar(SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (trans.Connection.State == ConnectionState.Closed)
            {
                trans.Connection.Open();
            }
            SqlCommand cmd = new SqlCommand(cmdText, trans.Connection);
            cmd.Transaction = trans;
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
            object val = cmd.ExecuteScalar();
            cmd.Dispose();

            return val;
        }
        /// <summary>
        /// 获取表中最大编号,带事务
        /// </summary>
        /// <param name="tbname"></param>
        /// <param name="fldname"></param>
        /// <returns></returns>
        public static int GetMaxId(SqlTransaction trans, string tbname, string fldname)
        {
            string s = "";
            try
            {
                if (trans.Connection.State == ConnectionState.Closed)
                {
                    trans.Connection.Open();
                }
                SqlCommand cmd = new SqlCommand("select MAX(CAST(" + fldname + " AS int)) from " + tbname, trans.Connection);
                cmd.Transaction = trans;
                s = cmd.ExecuteScalar().ToString();
                cmd.Dispose();
                if (s == "")
                    return 1;
                else
                    return Convert.ToInt32(s) + 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 操作数据库方法,带事务操作
        /// </summary>
        /// <param name="str"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(SqlTransaction trans, string str, SqlParameter[] sql)
        {
            try
            {
                if (trans.Connection.State == ConnectionState.Closed)
                {
                    trans.Connection.Open();
                }
                SqlCommand command = new SqlCommand(str, trans.Connection);
                command.Transaction = trans;
                if (sql != null)
                {
                    command.Parameters.AddRange(sql);
                }
                int ret = 0;
                ret = command.ExecuteNonQuery();
                command.Dispose();
                return ret;
            }
            catch (Exception E)
            {
                if (trans.Connection.State == ConnectionState.Open)
                {
                    trans.Connection.Close();
                }
                throw E;
            }
        }
        public static DataTable DataTable(SqlTransaction trans, string str, SqlParameter[] sql)
        {
            try
            {
                if (trans.Connection.State == ConnectionState.Closed)
                {
                    trans.Connection.Open();
                }
                SqlCommand command = new SqlCommand(str, trans.Connection);
                command.Transaction = trans;
                if (sql != null)
                {
                    command.Parameters.AddRange(sql);
                }
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = command;
                DataTable datatable = new DataTable();
                sqlDataAdapter.Fill(datatable);
                command.Dispose();
                return datatable;
            }
            catch (Exception E)
            {
                if (trans.Connection.State == ConnectionState.Open)
                {
                    trans.Connection.Close();
                }
                throw E;
            }
        }
        public static int ExecuteNonQuery(SqlTransaction trans, string str)
        {
            try
            {
                if (trans.Connection.State == ConnectionState.Closed)
                {
                    trans.Connection.Open();
                }
                SqlCommand command = new SqlCommand(str, trans.Connection);
                command.Transaction = trans;

                int ret = 0;
                ret = command.ExecuteNonQuery();
                command.Dispose();
                return ret;
            }
            catch (Exception E)
            {
                if (trans.Connection.State == ConnectionState.Open)
                {
                    trans.Connection.Close();
                }
                throw E;
            }
        }
        /// <summary>
        /// 返回受影响的行数,带事务重载
        /// </summary>
        /// <param name="trans"></param>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public static object ExecuteScalar(SqlTransaction trans, string cmdText)
        {
            SqlCommand cmd = new SqlCommand(cmdText, trans.Connection);
            cmd.Transaction = trans;

            object val = cmd.ExecuteScalar();

            return val;
        }
        /// <summary>
        /// 获取表中最大编号
        /// </summary>
        /// <param name="tbname"></param>
        /// <param name="fldname"></param>
        /// <returns></returns>
        public static int GetMaxId(string tbname, string fldname)
        {
            string s = "";
            SqlConnection con = new SqlConnection(ConnErp);
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                SqlCommand cmd = new SqlCommand("select MAX(CAST(" + fldname + " AS int)) from " + tbname, con);
                s = cmd.ExecuteScalar().ToString();
                cmd.Dispose();
                if (s == "")
                    return 1;
                else
                    return Convert.ToInt32(s) + 1;
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// 取区域中最大编号
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static int GetDeptId(string strSql)
        {
            string s = "";

            SqlConnection con = new SqlConnection(ConnErp);
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                SqlCommand cmd = new SqlCommand(strSql, con);
                s = cmd.ExecuteScalar().ToString();
                cmd.Dispose();
                if (s == "")
                    return 1;
                else if (s.Substring(s.Length - 3, 3) == "999")
                    return 999;
                else
                    return Convert.ToInt32(s.Substring(s.Length - 3, 3)) + 1;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 获取单个数据
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static object GetData(string strSql, int type)
        {
            object s = new object();
            SqlConnection con = new SqlConnection();

            if (type == 0)
            {
                con.ConnectionString = ConnErp;
            }
            else if (type == 1)
            {
                con.ConnectionString = ConnErpTool;
            }
            else if (type == 2)
            {
                con.ConnectionString = ConnBpm;
            }
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                SqlCommand cmd = new SqlCommand(strSql, con);
                s = cmd.ExecuteScalar().ToString();
                cmd.Dispose();
                return s;
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// 存储过程分页用
        /// </summary>
        /// <param name="tablename"></param>
        /// <param name="sqlParameter"></param>
        /// <returns></returns>
        public static DataTable StoredDataTable(string tablename, SqlParameter[] sqlParameter, int type)
        {
            using (SqlConnection sqlConnection = new SqlConnection())
            {

                if (type == 0)
                {
                    sqlConnection.ConnectionString = ConnErp;
                }
                else if (type == 1)
                {
                    sqlConnection.ConnectionString = ConnErpTool;
                }
                else if (type == 2)
                {
                    sqlConnection.ConnectionString = ConnBpm;
                }
                sqlConnection.Open();
                try
                {
                    SqlCommand sqlCommand = new SqlCommand();
                    sqlCommand.CommandText = tablename;
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Connection = sqlConnection;
                    sqlCommand.Parameters.AddRange(sqlParameter);
                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                    sqlDataAdapter.SelectCommand = sqlCommand;
                    DataTable datatable = new DataTable();
                    sqlDataAdapter.Fill(datatable);
                    sqlCommand.Parameters.Clear();
                    return datatable;
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    sqlConnection.Close();
                    sqlConnection.Dispose();
                }
            }

        }

        public static List<T> TableToList<T>(DataTable dt)
        {
            List<T> listT = new List<T>();

            Type type = typeof(T);

            if (dt == null)
            {
                return listT;
            }
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    T entity = Activator.CreateInstance<T>();

                    DataRow row = dt.Rows[i];

                    PropertyInfo[] pArray = type.GetProperties();

                    foreach (PropertyInfo p in pArray)
                    {
                        if (!dt.Columns.Contains(p.Name) || row[p.Name] == null || row[p.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        if (p.PropertyType == typeof(DateTime) && Convert.ToDateTime(row[p.Name]) < Convert.ToDateTime("1753-01-02"))
                        {
                            continue;
                        }
                        try
                        {
                            var obj = Convert.ChangeType(row[p.Name], p.PropertyType);
                            p.SetValue(entity, obj, null);
                        }
                        catch (Exception)
                        {
                            // throw;
                        }
                    }
                    listT.Add(entity);

                }
            }
            return listT;
        }
    }
}
